package com.ruoyi.ldzlsb.service.impl;

import java.util.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.ruoyi.common.core.utils.DateUtils;
import com.ruoyi.common.security.utils.SecurityUtils;
import com.ruoyi.ldzlsb.mapper.SbEquipmentMapper;
import com.ruoyi.ldzlsb.mapper.SbEquipmentStatusLogMapper;
import com.ruoyi.ldzlsb.domain.SbEquipmentStatusLog;
import com.ruoyi.ldzlsb.domain.vo.SbEquipmentVo;
import com.ruoyi.ldzlsb.service.ISbEquipmentService;
import com.ruoyi.ldzlsb.domain.SbEquipment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 设备台账Service业务层处理
 * 
 * @author ruoyi
 */
@Service
public class SbEquipmentServiceImpl implements ISbEquipmentService {
    private static final Logger log = LoggerFactory.getLogger(SbEquipmentServiceImpl.class);

    @Autowired
    private SbEquipmentMapper sbEquipmentMapper;
    
    @Autowired
    private SbEquipmentStatusLogMapper sbEquipmentStatusLogMapper;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired(required = false)
    private com.ruoyi.ldzlsb.mapper.SbEquipmentTypeMapper sbEquipmentTypeMapper;

    /**
     * 查询设备台账列表
     * 
     * @param sbEquipmentVo 设备台账
     * @return 设备台账
     */
    @Override
    public List<SbEquipmentVo> selectSbEquipmentList(SbEquipmentVo sbEquipmentVo) {
        // 从数据库查询设备列表
        return sbEquipmentMapper.selectSbEquipmentList(sbEquipmentVo);
    }

    /**
     * 查询设备台账详细
     * 
     * @param equipmentId 设备台账主键
     * @return 设备台账
     */
    @Override
    public SbEquipmentVo selectSbEquipmentByEquipmentId(Long equipmentId) {
        // 从数据库获取设备信息
        SbEquipment sbEquipment = sbEquipmentMapper.selectSbEquipmentByEquipmentID(equipmentId);
        if (sbEquipment == null) {
            return null;
        }
        
        // 转换为VO对象
        SbEquipmentVo vo = new SbEquipmentVo();
        vo.setEquipmentId(sbEquipment.getEquipmentId());
        vo.setEquipmentCode(sbEquipment.getEquipmentCode());
        vo.setEquipmentName(sbEquipment.getEquipmentName());
        vo.setEquipmentTypeId(sbEquipment.getEquipmentTypeId());
        vo.setEquipmentTypeName(sbEquipment.getEquipmentTypeName());
        vo.setEquipmentStatus(sbEquipment.getEquipmentStatus());
        vo.setSpecification(sbEquipment.getProductSfn());
        vo.setManufacturer(sbEquipment.getManufacturer());
        vo.setPurchaseDate(sbEquipment.getPurchaseDate());
        vo.setPurchasePrice(sbEquipment.getPurchasePrice());
        vo.setWarrantyPeriod(sbEquipment.getWarrantyPeriod());
        vo.setWarrantyEndDate(sbEquipment.getWarrantyEndDate());
        vo.setSerialNumber(sbEquipment.getSerialNumber());
        vo.setResponsiblePerson(sbEquipment.getResponsiblePerson());
        vo.setUserId(sbEquipment.getUserId()); // 添加用户ID字段映射
        vo.setUserNickName(sbEquipment.getManagerNickName()); // 添加用户昵称映射
        
        // 车间和生产线信息
        vo.setWorkshop(sbEquipment.getWorkshop());
        vo.setWorkshopId(sbEquipment.getWorkshopId());
        vo.setWorkshopName(sbEquipment.getWorkshopName());
        vo.setProductionLine(sbEquipment.getProductionLine());
        vo.setProductionLineId(sbEquipment.getProductionLineId());
        vo.setProductionLineName(sbEquipment.getProductionLineName());
        
        // 部门信息
        vo.setDepartmentId(sbEquipment.getDepartmentId());
        vo.setDepartmentName(sbEquipment.getDepartmentName());
        
        vo.setWeight(sbEquipment.getWeight());
        vo.setLocation(sbEquipment.getLocation());
        vo.setImageUrl(sbEquipment.getImageUrl());
        vo.setIsDelete(sbEquipment.getIsDelete());
        vo.setTechnicalParams(sbEquipment.getTechnicalDocs());
        vo.setContactPhone(sbEquipment.getContactPhone());
        vo.setCreateBy(sbEquipment.getCreateBy());
        vo.setCreateTime(sbEquipment.getCreateTime());
        vo.setUpdateBy(sbEquipment.getUpdateBy());
        vo.setUpdateTime(sbEquipment.getUpdateTime());
        vo.setRemark(sbEquipment.getRemark());
        vo.setModel(sbEquipment.getModel());
        vo.setSpecifications(sbEquipment.getSpecifications());
        
        return vo;
    }

    /**
     * 新增设备台账
     * 
     * @param sbEquipmentVo 设备台账
     * @return 结果
     */
    @Override
    public int insertSbEquipment(SbEquipmentVo sbEquipmentVo) {
        log.info("开始新增设备信息: 编码={}, 名称={}", sbEquipmentVo.getEquipmentCode(), sbEquipmentVo.getEquipmentName());
        
        // 检查设备编码是否已存在
        if (sbEquipmentVo.getEquipmentCode() != null) {
            log.info("检查设备编码是否已存在: {}", sbEquipmentVo.getEquipmentCode());
            int count = sbEquipmentMapper.checkEquipmentCodeExists(sbEquipmentVo.getEquipmentCode());
            if (count > 0) {
                log.error("设备编码已存在: {}", sbEquipmentVo.getEquipmentCode());
                throw new RuntimeException("设备编码 '" + sbEquipmentVo.getEquipmentCode() + "' 已存在，请更换");
            } else {
                log.info("设备编码可用");
            }
        } else {
            log.warn("设备编码为空");
            throw new RuntimeException("设备编码不能为空");
        }

        // 检查序列号是否已存在（如果提供）
        if (sbEquipmentVo.getSerialNumber() != null && !sbEquipmentVo.getSerialNumber().isEmpty()) {
            log.info("检查设备序列号是否已存在: {}", sbEquipmentVo.getSerialNumber());
            int count = sbEquipmentMapper.checkSerialNumberExists(sbEquipmentVo.getSerialNumber());
            if (count > 0) {
                log.error("设备序列号已存在: {}", sbEquipmentVo.getSerialNumber());
                throw new RuntimeException("序列号 '" + sbEquipmentVo.getSerialNumber() + "' 已存在，请更换");
            } else {
                log.info("设备序列号可用");
            }
        }
        
        try {
        // 转换为实体对象
        SbEquipment sbEquipment = new SbEquipment();
        sbEquipment.setEquipmentCode(sbEquipmentVo.getEquipmentCode());
        sbEquipment.setEquipmentName(sbEquipmentVo.getEquipmentName());
        sbEquipment.setEquipmentTypeId(sbEquipmentVo.getEquipmentTypeId());
        
        // 如果设备类型ID存在，尝试获取设备类型名称
        if (sbEquipmentVo.getEquipmentTypeId() != null) {
            String typeName = getEquipmentTypeName(sbEquipmentVo.getEquipmentTypeId());
            if (typeName != null && !typeName.isEmpty()) {
                sbEquipment.setEquipmentTypeName(typeName);
                log.info("从数据库获取设备类型名称: {}", typeName);
            } else {
                sbEquipment.setEquipmentTypeName(sbEquipmentVo.getEquipmentTypeName());
                log.info("使用前端传入的设备类型名称: {}", sbEquipmentVo.getEquipmentTypeName());
            }
        } else {
        sbEquipment.setEquipmentTypeName(sbEquipmentVo.getEquipmentTypeName());
        }
        
        sbEquipment.setEquipmentStatus(sbEquipmentVo.getEquipmentStatus());
        sbEquipment.setProductSfn(sbEquipmentVo.getSpecification());
        sbEquipment.setManufacturer(sbEquipmentVo.getManufacturer());
        sbEquipment.setPurchaseDate(sbEquipmentVo.getPurchaseDate());
        sbEquipment.setPurchasePrice(sbEquipmentVo.getPurchasePrice());
        sbEquipment.setWarrantyPeriod(sbEquipmentVo.getWarrantyPeriod());
        sbEquipment.setWarrantyEndDate(sbEquipmentVo.getWarrantyEndDate());
        sbEquipment.setSerialNumber(sbEquipmentVo.getSerialNumber());
        sbEquipment.setResponsiblePerson(sbEquipmentVo.getResponsiblePerson());
        sbEquipment.setUserId(sbEquipmentVo.getUserId()); // 添加用户ID字段映射
        sbEquipment.setManagerNickName(sbEquipmentVo.getUserNickName()); // 添加用户昵称映射
        
        // 添加设备型号和规格字段映射
        sbEquipment.setModel(sbEquipmentVo.getModel());
        sbEquipment.setSpecifications(sbEquipmentVo.getSpecifications());
        
        // 车间和生产线信息
        sbEquipment.setWorkshop(sbEquipmentVo.getWorkshop());
        sbEquipment.setWorkshopId(sbEquipmentVo.getWorkshopId());
        sbEquipment.setWorkshopName(sbEquipmentVo.getWorkshopName());
        sbEquipment.setProductionLine(sbEquipmentVo.getProductionLine());
        sbEquipment.setProductionLineId(sbEquipmentVo.getProductionLineId());
        sbEquipment.setProductionLineName(sbEquipmentVo.getProductionLineName());
        
        // 部门信息 - 现在单独存储，不再与车间混淆
        sbEquipment.setDepartmentId(sbEquipmentVo.getDepartmentId());
        sbEquipment.setDepartmentName(sbEquipmentVo.getDepartmentName());
        
        sbEquipment.setWeight(sbEquipmentVo.getWeight());
        sbEquipment.setLocation(sbEquipmentVo.getLocation());
        sbEquipment.setImageUrl(sbEquipmentVo.getImageUrl());
        sbEquipment.setIsDelete("0");
        sbEquipment.setTechnicalDocs(sbEquipmentVo.getTechnicalParams());
        // 不再使用联系电话字段
        sbEquipment.setResponsiblePerson(sbEquipmentVo.getResponsiblePerson());
        sbEquipment.setDepartmentName(sbEquipmentVo.getDepartmentName());
        sbEquipment.setRemark(sbEquipmentVo.getRemark());
        sbEquipment.setCreateBy(SecurityUtils.getUsername());
            sbEquipment.setCreateTime(DateUtils.getNowDate());
        
            log.info("执行设备插入操作");
            int result = sbEquipmentMapper.insertSbEquipment(sbEquipment);
            log.info("设备插入完成: ID={}", sbEquipment.getEquipmentId());
            return result;
        } catch (Exception e) {
            log.error("设备插入失败", e);
            throw new RuntimeException("新增设备失败: " + e.getMessage());
        }
    }
    
    /**
     * 修改设备台账
     * 
     * @param sbEquipmentVo 设备台账
     * @return 结果
     */
    @Override
    public int updateSbEquipment(SbEquipmentVo sbEquipmentVo) {
        log.info("开始更新设备信息: ID={}, 编码={}", sbEquipmentVo.getEquipmentId(), sbEquipmentVo.getEquipmentCode());
        
        // 先获取原始设备信息
        SbEquipment originalEquipment = null;
        if (sbEquipmentVo.getEquipmentId() != null) {
            originalEquipment = sbEquipmentMapper.selectSbEquipmentByEquipmentID(sbEquipmentVo.getEquipmentId());
            if (originalEquipment != null) {
                log.info("获取到原始设备信息: ID={}, 原始编码={}", originalEquipment.getEquipmentId(), originalEquipment.getEquipmentCode());
                
                // 如果编码没有变更，则直接跳过编码重复检查
                if (originalEquipment.getEquipmentCode() != null && 
                    sbEquipmentVo.getEquipmentCode() != null && 
                    originalEquipment.getEquipmentCode().equals(sbEquipmentVo.getEquipmentCode())) {
                    log.info("设备编码未变更，跳过重复检查");
                } 
                // 只有编码变更时才检查重复
                else if (sbEquipmentVo.getEquipmentCode() != null) {
                    log.info("检查新编码是否重复: {}", sbEquipmentVo.getEquipmentCode());
                    
                    // 排除自身ID检查编码是否存在
                    int count = sbEquipmentMapper.checkEquipmentCodeExistsExcludeSelf(
                        sbEquipmentVo.getEquipmentCode(), sbEquipmentVo.getEquipmentId());
                        
                    if (count > 0) {
                        log.error("设备编码已存在: {}", sbEquipmentVo.getEquipmentCode());
                        throw new RuntimeException("设备编码 '" + sbEquipmentVo.getEquipmentCode() + "' 已存在，请更换");
                    } else {
                        log.info("编码可用，可以更新");
                    }
                }
            } else {
                log.warn("未找到原始设备信息: ID={}", sbEquipmentVo.getEquipmentId());
                
                // 如果无法获取原设备信息，仍然执行编码检查
                if (sbEquipmentVo.getEquipmentCode() != null) {
                    int count = sbEquipmentMapper.checkEquipmentCodeExistsExcludeSelf(
                        sbEquipmentVo.getEquipmentCode(), sbEquipmentVo.getEquipmentId());
                        
                    if (count > 0) {
                        throw new RuntimeException("设备编码 '" + sbEquipmentVo.getEquipmentCode() + "' 已存在，请更换");
                    }
                }
            }
        }
        
        // 转换为实体对象
        SbEquipment sbEquipment = new SbEquipment();
        sbEquipment.setEquipmentId(sbEquipmentVo.getEquipmentId());
        sbEquipment.setEquipmentCode(sbEquipmentVo.getEquipmentCode());
        sbEquipment.setEquipmentName(sbEquipmentVo.getEquipmentName());
        sbEquipment.setEquipmentTypeId(sbEquipmentVo.getEquipmentTypeId());
        
        // 如果设备类型ID存在，尝试获取设备类型名称
        if (sbEquipmentVo.getEquipmentTypeId() != null) {
            try {
                String typeName = getEquipmentTypeName(sbEquipmentVo.getEquipmentTypeId());
                if (typeName != null && !typeName.isEmpty()) {
                    sbEquipment.setEquipmentTypeName(typeName);
                } else {
                    sbEquipment.setEquipmentTypeName(sbEquipmentVo.getEquipmentTypeName());
                }
            } catch (Exception e) {
                log.warn("获取设备类型名称失败，使用前端传递的类型名称", e);
                sbEquipment.setEquipmentTypeName(sbEquipmentVo.getEquipmentTypeName());
            }
        } else {
        sbEquipment.setEquipmentTypeName(sbEquipmentVo.getEquipmentTypeName());
        }
        
        sbEquipment.setEquipmentStatus(sbEquipmentVo.getEquipmentStatus());
        sbEquipment.setProductSfn(sbEquipmentVo.getSpecification());
        sbEquipment.setManufacturer(sbEquipmentVo.getManufacturer());
        sbEquipment.setPurchaseDate(sbEquipmentVo.getPurchaseDate());
        sbEquipment.setPurchasePrice(sbEquipmentVo.getPurchasePrice());
        sbEquipment.setWarrantyPeriod(sbEquipmentVo.getWarrantyPeriod());
        sbEquipment.setWarrantyEndDate(sbEquipmentVo.getWarrantyEndDate());
        sbEquipment.setSerialNumber(sbEquipmentVo.getSerialNumber());
        sbEquipment.setResponsiblePerson(sbEquipmentVo.getResponsiblePerson());
        sbEquipment.setUserId(sbEquipmentVo.getUserId()); // 添加用户ID字段映射
        sbEquipment.setManagerNickName(sbEquipmentVo.getUserNickName()); // 添加用户昵称映射
        
        // 添加设备型号和规格字段映射
        sbEquipment.setModel(sbEquipmentVo.getModel());
        sbEquipment.setSpecifications(sbEquipmentVo.getSpecifications());
        
        // 车间和生产线信息
        sbEquipment.setWorkshop(sbEquipmentVo.getWorkshop());
        sbEquipment.setWorkshopId(sbEquipmentVo.getWorkshopId());
        sbEquipment.setWorkshopName(sbEquipmentVo.getWorkshopName());
        sbEquipment.setProductionLine(sbEquipmentVo.getProductionLine());
        sbEquipment.setProductionLineId(sbEquipmentVo.getProductionLineId());
        sbEquipment.setProductionLineName(sbEquipmentVo.getProductionLineName());
        
        // 部门信息
        sbEquipment.setDepartmentId(sbEquipmentVo.getDepartmentId());
        sbEquipment.setDepartmentName(sbEquipmentVo.getDepartmentName());
        
        sbEquipment.setWeight(sbEquipmentVo.getWeight());
        sbEquipment.setLocation(sbEquipmentVo.getLocation());
        sbEquipment.setImageUrl(sbEquipmentVo.getImageUrl());
        sbEquipment.setTechnicalDocs(sbEquipmentVo.getTechnicalParams());
        sbEquipment.setRemark(sbEquipmentVo.getRemark());
        sbEquipment.setUpdateBy(SecurityUtils.getUsername());
        sbEquipment.setUpdateTime(DateUtils.getNowDate());
        
        log.info("执行设备更新操作: ID={}", sbEquipmentVo.getEquipmentId());
        return sbEquipmentMapper.updateSbEquipment(sbEquipment);
    }
    
    /**
     * 批量删除设备台账
     * 
     * @param equipmentIds 需要删除的设备台账主键
     * @return 结果
     */
    @Override
    public int deleteSbEquipmentByIds(Long[] equipmentIds) {
        // 逻辑删除设备记录
        int rows = 0;
        for (Long equipmentId : equipmentIds) {
            rows += sbEquipmentMapper.logicDeleteSbEquipment(equipmentId);
        }
        return rows;
    }
    
    /**
     * 生成设备编码
     * 
     * @param type 设备类型编码
     * @return 设备编码
     */
    @Override
    public String generateEquipmentCode(String type) {
        try {
            log.info("正在生成设备编码，类型: {}", type);
            
        // 使用日期和随机数生成设备编码
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String date = sdf.format(new Date());
        
            // 简单随机数，确保使用3位数，前面补0
        int random = new Random().nextInt(1000);
        String randomStr = String.format("%03d", random);
        
        // 设备类型前缀，默认为SB
        String prefix = "SB";
            if (type != null && !type.isEmpty() && !type.equals("null")) {
                prefix = type.trim();
                // 如果前缀包含特殊字符，只保留字母和数字
                prefix = prefix.replaceAll("[^a-zA-Z0-9]", "");
                if (prefix.isEmpty()) {
                    prefix = "SB";
                }
            }
            
            String code = prefix + "-" + date + "-" + randomStr;
            log.info("生成的设备编码: {}", code);
            
            // 检查编码是否已存在
            int count = sbEquipmentMapper.checkEquipmentCodeExists(code);
            if (count > 0) {
                log.info("编码 {} 已存在，重新生成", code);
                // 如果编码已存在，递归调用本方法重新生成
                return generateEquipmentCode(type);
            }
            
            return code;
        } catch (Exception e) {
            log.error("生成设备编码失败", e);
            // 确保即使发生异常也能返回一个可用的编码
            String fallbackCode = "SB-" + System.currentTimeMillis();
            log.info("返回备用编码: {}", fallbackCode);
            return fallbackCode;
        }
    }

    /**
     * 更新设备状态
     * 
     * @param equipmentId 设备ID
     * @param status 状态
     * @param reason 原因
     * @return 结果
     */
    @Override
    @Transactional
    public int updateStatus(Long equipmentId, String status, String reason) {
        // 获取设备当前信息
        SbEquipmentVo equipment = selectSbEquipmentByEquipmentId(equipmentId);
        if (equipment == null) {
            return 0;
        }
        
        // 当前状态与要更新的状态相同，不进行操作
        if (equipment.getEquipmentStatus().equals(status)) {
            return 1;
        }
        
        // 记录状态变更日志
        SbEquipmentStatusLog statusLog = new SbEquipmentStatusLog();
        statusLog.setEquipmentId(equipmentId);
        statusLog.setEquipmentCode(equipment.getEquipmentCode());
        statusLog.setOldStatus(equipment.getEquipmentStatus());
        statusLog.setNewStatus(status);
        statusLog.setChangeReason(reason);
        statusLog.setChangeTime(DateUtils.getNowDate());
        statusLog.setOperatorName(SecurityUtils.getUsername());
        statusLog.setCreateBy(SecurityUtils.getUsername());
        statusLog.setCreateTime(DateUtils.getNowDate());
        
        // 插入状态变更日志
        sbEquipmentStatusLogMapper.insertSbEquipmentStatusLog(statusLog);
        
        // 更新设备状态
        equipment.setEquipmentStatus(status);
        equipment.setUpdateBy(SecurityUtils.getUsername());
        equipment.setUpdateTime(DateUtils.getNowDate());
        
        // 将SbEquipmentVo转换为SbEquipment
        SbEquipment updateEquipment = new SbEquipment();
        updateEquipment.setEquipmentId(equipment.getEquipmentId());
        updateEquipment.setEquipmentStatus(status);
        updateEquipment.setUpdateBy(SecurityUtils.getUsername());
        updateEquipment.setUpdateTime(DateUtils.getNowDate());
        
        // 更新设备状态到数据库
        return sbEquipmentMapper.updateSbEquipment(updateEquipment);
    }
    
    /**
     * 获取设备状态统计
     * 
     * @return 状态统计
     */
    @Override
    public Map<String, Object> getStatusStats() {
        Map<String, Object> stats = new HashMap<>();
        
        // 获取设备总数
        int total = sbEquipmentMapper.selectEquipmentCount();
        stats.put("total", total);
        
        // 获取各状态设备数量
        List<Map<String, Object>> statusCounts = sbEquipmentMapper.selectEquipmentStatusCount();
        Map<String, Integer> statusMap = new HashMap<>();
        statusMap.put("idle", 0);      // 闲置
        statusMap.put("inUse", 0);     // 使用中
        statusMap.put("repairing", 0); // 维修中
        statusMap.put("scrapped", 0);  // 报废
        
        for (Map<String, Object> item : statusCounts) {
            String status = (String) item.get("status");
            Long count = (Long) item.get("count");
            
            switch (status) {
                case "0":
                    statusMap.put("idle", count.intValue());
                    break;
                case "1":
                    statusMap.put("inUse", count.intValue());
                    break;
                case "2":
                    statusMap.put("repairing", count.intValue());
                    break;
                case "3":
                    statusMap.put("scrapped", count.intValue());
                    break;
                default:
                    break;
            }
        }
        
        stats.put("statusCounts", statusMap);
        
        // 获取车间设备分布
        List<Map<String, Object>> workshopCounts = sbEquipmentMapper.selectEquipmentWorkshopStatusCount();
        stats.put("workshopCounts", workshopCounts);
        
        return stats;
    }

    @Override
    public List<Map<String, Object>> getInspectionRecords(Long equipmentId) {
        String sql = "SELECT " +
                "r.record_id as id, " +
                "r.record_code as recordCode, " +
                "r.inspection_date as inspectionTime, " +
                "r.inspector_name as inspectionPerson, " +
                "CASE r.inspection_result " +
                "   WHEN '0' THEN 'pass' " +
                "   WHEN '1' THEN 'fail' " +
                "   ELSE 'unknown' " +
                "END as status, " +
                "r.remark " +
                "FROM sb_inspection_record r " +
                "WHERE r.equipment_id = ? " +
                "AND (r.is_delete = '0' OR r.is_delete IS NULL) " +
                "ORDER BY r.inspection_date DESC";
        
        try {
            return jdbcTemplate.queryForList(sql, equipmentId);
        } catch (Exception e) {
            log.error("获取设备点检记录失败", e);
            return new ArrayList<>();
        }
    }

    @Override
    public List<Map<String, Object>> getMaintenanceRecords(Long equipmentId) {
        String sql = "SELECT " +
                "r.record_id as id, " +
                "r.record_code as orderCode, " +
                "r.maintenance_date as maintenanceTime, " +
                "r.maintainer_name as maintenancePerson, " +
                "CASE r.maintenance_type " +
                "   WHEN '0' THEN 'daily' " +
                "   WHEN '1' THEN 'weekly' " +
                "   WHEN '2' THEN 'monthly' " +
                "   WHEN '3' THEN 'quarterly' " +
                "   WHEN '4' THEN 'yearly' " +
                "   ELSE 'other' " +
                "END as maintenanceType, " +
                "CASE r.maintenance_status " +
                "   WHEN '0' THEN 'pending' " +
                "   WHEN '1' THEN 'processing' " +
                "   WHEN '2' THEN 'completed' " +
                "   ELSE 'unknown' " +
                "END as status, " +
                "r.remark " +
                "FROM sb_maintenance_record r " +
                "WHERE r.equipment_id = ? " +
                "AND (r.is_delete = '0' OR r.is_delete IS NULL) " +
                "ORDER BY r.maintenance_date DESC";
        
        try {
            return jdbcTemplate.queryForList(sql, equipmentId);
        } catch (Exception e) {
            log.error("获取设备保养记录失败", e);
            return new ArrayList<>();
        }
    }

    @Override
    public List<Map<String, Object>> getRepairRecords(Long equipmentId) {
        String sql = "SELECT " +
                "r.repair_id as id, " +
                "r.repair_code as orderCode, " +
                "r.report_time as repairTime, " +
                "r.handle_person as repairPerson, " +
                "r.fault_type as faultType, " +
                "r.status, " +
                "r.remark " +
                "FROM sb_repair_order r " +
                "WHERE r.equipment_id = ? " +
                "AND (r.is_delete = '0' OR r.is_delete IS NULL) " +
                "ORDER BY r.report_time DESC";
        
        try {
            return jdbcTemplate.queryForList(sql, equipmentId);
        } catch (Exception e) {
            log.error("获取设备维修记录失败", e);
            return new ArrayList<>();
        }
    }

    /**
     * 根据设备类型ID删除设备
     * 
     * @param equipmentTypeId 设备类型ID
     * @return 结果
     */
    @Override
    public int deleteSbEquipmentByTypeId(Long equipmentTypeId) {
        try {
            // 构造查询对象
            SbEquipmentVo query = new SbEquipmentVo();
            query.setEquipmentTypeId(equipmentTypeId);
            
            // 查询指定类型的所有设备
            List<SbEquipmentVo> equipmentList = sbEquipmentMapper.selectSbEquipmentList(query);
            
            if (equipmentList != null && !equipmentList.isEmpty()) {
                // 提取设备ID
                Long[] equipmentIds = equipmentList.stream()
                    .map(SbEquipmentVo::getEquipmentId)
                    .toArray(Long[]::new);
                
                // 批量逻辑删除
                if (equipmentIds.length > 0) {
                    return deleteSbEquipmentByIds(equipmentIds);
                }
            }
            
            return 0;
        } catch (Exception e) {
            log.error("删除设备类型关联设备失败", e);
            return 0;
        }
    }

    /**
     * 检查设备编码是否已存在（排除自身ID）
     * 
     * @param equipmentCode 设备编码
     * @param equipmentId 设备ID（需要排除的ID）
     * @return 结果
     */
    @Override
    public int checkEquipmentCodeExistsExcludeSelf(String equipmentCode, Long equipmentId) {
        return sbEquipmentMapper.checkEquipmentCodeExistsExcludeSelf(equipmentCode, equipmentId);
    }

    /**
     * 检查设备编码是否已存在
     * 
     * @param equipmentCode 设备编码
     * @return 结果
     */
    @Override
    public int checkEquipmentCodeExists(String equipmentCode) {
        return sbEquipmentMapper.checkEquipmentCodeExists(equipmentCode);
    }
    
    /**
     * 统计指定类型的设备数量
     * 
     * @param typeId 设备类型ID
     * @return 设备数量
     */
    @Override
    public int countEquipmentByTypeId(Long typeId) {
        try {
            // 使用JDBC直接查询设备表
            String sql = "SELECT COUNT(*) FROM sb_equipment WHERE equipment_type_id = ? AND (is_delete = '0' OR is_delete IS NULL)";
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class, typeId);
            return count != null ? count : 0;
        } catch (Exception e) {
            log.error("统计设备类型数量失败: typeId={}", typeId, e);
            return 0;
        }
    }
    
    /**
     * 根据设备类型ID获取类型名称
     * 
     * @param typeId 设备类型ID
     * @return 设备类型名称
     */
    private String getEquipmentTypeName(Long typeId) {
        if (typeId == null) {
            return null;
        }
        
        try {
            // 使用JDBC直接查询设备类型表
            String sql = "SELECT type_name FROM sb_equipment_type WHERE type_id = ? AND (is_delete = '0' OR is_delete IS NULL)";
            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, typeId);
            
            if (result != null && !result.isEmpty()) {
                Map<String, Object> row = result.get(0);
                return (String) row.get("type_name");
            }
        } catch (Exception e) {
            log.error("查询设备类型名称失败: typeId={}", typeId, e);
        }
        
        return null;
    }
}
